﻿using System;
using System.Collections.Generic;
using System.Text;
using GDA.Analysis;
using System.Data;

namespace GDA.Provider.Oracle
{
    public class OracleAnalyzer : DatabaseAnalyzer
    {
        private const string ORA_VERSION_SELECT =
            @"select VERSION ""Version"" from PRODUCT_COMPONENT_VERSION where lower(product) like ('%oracle%')";
        // this was old old version (doesn't work on Personal edition):
        //		@"select VERSION ""Version"" from PRODUCT_COMPONENT_VERSION where PRODUCT like '%Oracle%'";
        // this doesn't work on Personal edition:
        //		@"select VERSION ""Version"" from PRODUCT_COMPONENT_VERSION where SUBSTR(PRODUCT,1,6)='Oracle'";
        // this requires admin privileges:
        //		@"select VERSION ""Version"" from V$INSTANCE"; 

        private const string ORA9_SELECT =
            @"select TAB.TABLE_NAME       ""TableName"",  " +
                @"		   TAB.COLUMN_NAME      ""ColumnName"",  " +
                @"		   TAB.DATA_TYPE        ""Type"",  " +
                @"		   TAB.DATA_LENGTH      ""Size"",  " +
                @"		   TAB.NULLABLE         ""IsNullable"",  " +
                @"		   TAB.DATA_DEFAULT     ""DefaultValue"",  " +
                @"		   CO.CONSTRAINT_NAME   ""ConstraintName"", " +
                @"		   CO.CONSTRAINT_TYPE   ""ConstraintType"", " +
                @"		   CO.R_CONSTRAINT_NAME ""ConstraintReference"", " +
                @"		   CO.DELETE_RULE       ""DeleteRule"", " +
                @"         CM.COMMENTS 			""TableComment"", " +
                @"         CC.COMMENTS			""ColumnComment"" " +
                @"from USER_TAB_COLUMNS TAB LEFT OUTER JOIN " +
                @"	     (USER_CONSTRAINTS CO INNER JOIN USER_CONS_COLUMNS CO1 ON " +
                @"		   CO.TABLE_NAME = CO1.TABLE_NAME AND CO.CONSTRAINT_NAME = CO1.CONSTRAINT_NAME) ON " +
                @"	   TAB.TABLE_NAME = CO.TABLE_NAME AND TAB.COLUMN_NAME = CO1.COLUMN_NAME " +
                @"     LEFT JOIN USER_TAB_COMMENTS CM ON TAB.TABLE_NAME = CM.TABLE_NAME " +
                @"     LEFT JOIN USER_COL_COMMENTS CC ON TAB.TABLE_NAME = CC.TABLE_NAME AND TAB.COLUMN_NAME = CC.COLUMN_NAME " +
                @"order by TAB.TABLE_NAME, TAB.COLUMN_NAME, CO1.CONSTRAINT_NAME";


        private const string ORA8_SELECT =
            @"select TAB.TABLE_NAME       ""TableName"", " +
                @"       TAB.COLUMN_NAME      ""ColumnName"", " +
                @"       TAB.DATA_TYPE        ""Type"", " +
                @"       TAB.DATA_LENGTH      ""Size"", " +
                @"       TAB.NULLABLE         ""IsNullable"", " +
                @"       TAB.DATA_DEFAULT     ""DefaultValue"", " +
                @"       CO.CONSTRAINT_NAME   ""ConstraintName"", " +
                @"       CO.CONSTRAINT_TYPE   ""ConstraintType"", " +
                @"       CO.R_CONSTRAINT_NAME ""ConstraintReference"", " +
                @"       CO.DELETE_RULE       ""DeleteRule"" " +
                @"from USER_TAB_COLUMNS TAB, USER_CONSTRAINTS CO, USER_CONS_COLUMNS CO1 " +
                @"where (TAB.TABLE_NAME = CO1.TABLE_NAME(+)) and " +
                @"      (TAB.COLUMN_NAME = CO1.COLUMN_NAME(+)) and " +
                @"      (CO1.constraint_name = CO.constraint_name(+)) and " +
                @"      (CO1.TABLE_NAME = CO.TABLE_NAME(+)) " +
                @"order by TAB.TABLE_NAME, TAB.COLUMN_NAME, CO1.CONSTRAINT_NAME";

        private const string ORA9_SELECT_REFERENCES =
            @"select CO.TABLE_NAME        ""ParentTable"", " +
                @"		   CO1.COLUMN_NAME      ""ParentColumn"", " +
                @"		   CO.CONSTRAINT_NAME   ""ConstraintName"", " +
                @"		   CO.R_CONSTRAINT_NAME ""ConstraintReference"", " +
                @"		   IDX2.TABLE_NAME      ""ChildTable"", " +
                @"		   IDX2.COLUMN_NAME     ""ChildColumn"" " +
                @"from (USER_CONSTRAINTS CO INNER JOIN USER_CONS_COLUMNS CO1 ON " +
                @"			   CO.TABLE_NAME = CO1.TABLE_NAME AND CO.CONSTRAINT_NAME = CO1.CONSTRAINT_NAME) " +
                @"		 LEFT OUTER JOIN USER_IND_COLUMNS IDX2 ON " +
                @"			  CO.R_CONSTRAINT_NAME = IDX2.INDEX_NAME AND CO1.POSITION = IDX2.COLUMN_POSITION " +
                @"where CO.CONSTRAINT_TYPE = 'R' AND " +
                @"      CO.R_CONSTRAINT_NAME = '{0}'";

        private const string ORA8_SELECT_REFERENCES =
            @"select CO.TABLE_NAME        ""ParentTable"", " +
                @"       CO1.COLUMN_NAME      ""ParentColumn"", " +
                @"       CO.CONSTRAINT_NAME   ""ConstraintName"", " +
                @"       CO.R_CONSTRAINT_NAME ""ConstraintReference"", " +
                @"       IDX2.TABLE_NAME      ""ChildTable"", " +
                @"       IDX2.COLUMN_NAME     ""ChildColumn"" " +
                @"from USER_CONSTRAINTS CO, USER_CONS_COLUMNS CO1, USER_IND_COLUMNS IDX2 " +
                @"where CO.TABLE_NAME = CO1.TABLE_NAME AND " +
                @"      CO.CONSTRAINT_NAME = CO1.CONSTRAINT_NAME AND " +
                @"      CO.R_CONSTRAINT_NAME = IDX2.INDEX_NAME AND " +
                @"      CO1.POSITION = IDX2.COLUMN_POSITION AND " +
                @"      CO.CONSTRAINT_TYPE = 'R' AND " +
                @"      CO.R_CONSTRAINT_NAME = '{0}'";

        // This query is constructed from above by unfolding and optimizing views
        // because Oracle 8.1.6 executes query ORA8_SELECT_REFERENCES extremely poor 
        // ORA816_SELECT_REFERENCES query is tested only with Oracle 8.1.6
        private const string ORA816_SELECT_REFERENCES =
            @"select O.NAME ""ParentTable"", " +
                @"	 COL.NAME ""ParentColumn"", " +
                @"	 OC.NAME ""ConstraintName"", " +
                @"	 RC.NAME ""ConstraintReference"", " +
                @"	 BASE.NAME  ""ChildTable"", " +
                @"	 IDC.NAME ""ChildColumn""	" +
                @"from SYS.CON$ OC, SYS.CON$ RC, SYS.CDEF$ C, SYS.OBJ$ O, SYS.COL$ COL, " +
                @"     SYS.CCOL$ CC, SYS.OBJ$ IDX, SYS.IND$ I, SYS.ICOL$ IC, SYS.OBJ$ BASE, SYS.COL$ IDC " +
                @"where OC.CON# = C.CON# and C.RCON# = RC.CON# and C.TYPE# = 4 and C.OBJ# = O.OBJ# " +
                @"	and C.CON# = CC.CON# and CC.OBJ# = COL.OBJ# and CC.INTCOL# = COL.INTCOL# " +
                @"	and IDX.OWNER# = O.OWNER# and RC.NAME = IDX.NAME and IDX.OBJ# = I.OBJ# " +
                @"	and I.TYPE# IN (1, 2, 3, 4, 6, 7, 9) and IC.OBJ# = IDX.OBJ# and IC.BO# = BASE.OBJ# " +
                @"  and IDC.OBJ# = BASE.OBJ# and IC.INTCOL# = IDC.INTCOL# and O.OWNER# = USERENV('SCHEMAID') " +
                @"  and RC.NAME =  '{0}' ";


        private static bool GetBoolean(string boolean)
        {
            string[] valids = new string[] { "yes", "true", "1", "y" };
            boolean = boolean == null ? "false" : boolean.ToLower();
            bool result = false;
            foreach (string valid in valids)
            {
                result |= valid.Equals(boolean);
            }
            return result;
        }

        #region Construtores

        public OracleAnalyzer(OracleProviderConfiguration provider)
            : base(provider)
		{
        }

        #endregion

        public override void Analyze(string tableName)
        {
            // Cria uma conexão
            IDbConnection conn = ProviderConfiguration.CreateConnection();
            // Cria uma instância para executar a consulta
            IDbCommand cmd = conn.CreateCommand();
            IDbDataAdapter da = ProviderConfiguration.Provider.CreateDataAdapter();
            da.SelectCommand = cmd;

            // Relaciona a conexão com o comando
            cmd.Connection = conn;

            conn.Open();

            try
            {
                // Verifica se serao carregado os dados de apenas uma tabela
                bool isSingleRun = tableName != null;

                // Define o consulta SQL para recuperar a versao do banco de dados
                cmd.CommandText = ORA_VERSION_SELECT;

                // Recupera a versao do BD
                string ver = cmd.ExecuteScalar().ToString();

                int indexOfDot = ver.IndexOf(".");
                if (indexOfDot < 0)
                {
                    throw new GDAException("Unable to determine Oracle database version.");
                }

                // Recupera o primeiro numero da verao
                int version = Convert.ToInt32(ver.Substring(0, indexOfDot));

                string select;
                string selectReferences;
                if (version < 9)
                {
                    // If Oracle version == '8.1.6' use no-views selectReferences
                    if (ver.Substring(0, 5).CompareTo("8.1.6") == 0)
                    {
                        selectReferences = ORA816_SELECT_REFERENCES;
                    }
                    else
                    {
                        selectReferences = ORA8_SELECT_REFERENCES;
                    }
                    select = ORA8_SELECT;
                }
                else
                {
                    select = ORA9_SELECT;
                    selectReferences = ORA9_SELECT_REFERENCES;
                }

                da.SelectCommand.CommandText = select;

                DataSet ds = new DataSet();

                da.Fill(ds);

                // Define um DataTable para salva o resultado
                DataTable dt = ds.Tables[0];

                // Processa o resultado das seguintes colunas:
                // TableName, ColumnName, Type, Size, IsNullable, DefaultValue, 
                // ConstraintName, ConstraintReference, ConstraintType, UpdateRule, DeleteRule
                for(int i=0; i<dt.Rows.Count; i++)
                {
                    try
                    {
                        // Recupera o nome da tabela
                        string dbTableName = (string)dt.Rows[i]["TableName"];
                        if (!isSingleRun || tableName.ToLower() == dbTableName.ToLower())
                        {
                            // get or create TableMap for table 
                            TableMap map = GetTableMap(dbTableName);
                            if (map == null)
                            {
                                map = new TableMap(ProviderConfiguration, dbTableName);
                                if (dt.Rows[i]["TableComment"] != DBNull.Value)
                                    map.Comment = (string)dt.Rows[i]["TableComment"];
                                this.tablesMaps[dbTableName.ToLower()] = map;
                            }
                            // Recupera o nome da coluna
                            string columnName = (string)dt.Rows[i]["ColumnName"];
                            FieldMap fm = map.GetFieldMapFromColumn(columnName);
                            if (fm == null)
                            {
                                fm = new FieldMap(map, columnName);

                                if (dt.Rows[i]["ColumnComment"] != DBNull.Value)
                                    fm.Comment = (string)dt.Rows[i]["ColumnComment"];

                                map.Fields.Add(fm);
                            }
                            
                            // Recupera as informacoes basicas

                            string typeInfo = (string)dt.Rows[i]["Type"];
                            int pos = typeInfo.IndexOf( "(" );
			                if( pos != -1 )
                                typeInfo = typeInfo.Substring(0, pos);

                            // Recupera o tipo da coluna
                            fm.SetDbType(typeInfo, false);
                            // Recupera se a coluna aceita valores nulos
                            fm.IsNullable = GetBoolean((string)dt.Rows[i]["IsNullable"]);

                            // Verifica se o tamanho da coluna foi recuperado
                            if (dt.Rows[i]["Size"] != DBNull.Value)
                                fm.Size = Convert.ToInt32(dt.Rows[i]["Size"]);

                            // Verifica se as informacoes sobre contraints foi recuperado
                            if (dt.Rows[i]["ConstraintName"] != DBNull.Value)
                            {
                                string typ = (string)dt.Rows[i]["ConstraintType"];
                                if (typ.ToLower() == "p")
                                {
                                    fm.IsPrimaryKey = true;
                                }
                                else if (typ.ToLower() == "r")
                                {
                                    string conref = (string)dt.Rows[i]["ConstraintReference"];
                                    cmd.CommandText = String.Format(selectReferences, conref);

                                    using (IDataReader dr = cmd.ExecuteReader())
                                    {
                                        if (dr.Read())
                                        {
                                            fm.ForeignKeyTableName = dr.GetString(dr.GetOrdinal("ChildTable"));
                                            fm.ForeignKeyColumnName = dr.GetString(dr.GetOrdinal("ChildColumn"));
                                        }
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception fe)
                    {
                        // ignore errors caused by tables found in db but for which no map exists
                        // TODO this should be a config option

                    }
                }
            }
            catch (Exception e)
            {
                throw new GDAException("An error occurred while analyzing the database schema.", e);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
